package com.gxy.learn.office.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpMethod;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * excel操作工具类
 */
@Slf4j
    public class ExcelUtils {
    public static void main(String[] args) throws IOException {
        String filePath = "/Users/gaoxueyong/Documents/export/excle文件测试.xlsx";
//            filePath = "/Users/gaoxueyong/Documents/export/excle文件测试.xls";


        //导出文件路径

        List<ExcelVo> result = new ArrayList<>();
        ExcelVo excelVo = new ExcelVo();
        excelVo.setSheetName("画江湖之不良人");
        excelVo.setSheetHead(Arrays.asList("姓名", "学号", "性别", "入学日期"));
        List<List<String>> sheetData = new ArrayList<>();
        sheetData.add(Arrays.asList("岐王", "17232401001", "男", "2015年9月"));
        sheetData.add(Arrays.asList("女帝", "17232401002", "女", "2016年9月"));
        sheetData.add(Arrays.asList("蚩梦", "17232401002", "女", "2016年9月"));
        sheetData.add(Arrays.asList("雪儿", "17232401002", "女", "2016年9月"));
        sheetData.add(Arrays.asList("老李", "17232401002", "南", "2016年9月"));
        excelVo.setSheetData(sheetData);
        result.add(excelVo);

        excelVo = new ExcelVo();
        excelVo.setSheetName("射雕英雄传");
        excelVo.setSheetHead(Arrays.asList("姓名", "学号", "性别", "入学日期"));
        sheetData = new ArrayList<>();
        sheetData.add(Arrays.asList("东邪", "17232401001", "男", "2015年9月"));
        sheetData.add(Arrays.asList("西毒", "17232401001", "男", "2015年9月"));
        sheetData.add(Arrays.asList("南帝", "17232401003", "男", "2017年9月"));
        sheetData.add(Arrays.asList("北丐", "17232401004", "男", "2015年9月"));
        sheetData.add(Arrays.asList("中神通", "17232401005", "南", "2017年9月"));
        excelVo.setSheetData(sheetData);
        result.add(excelVo);
        writeExcelXlsx(filePath, result);
        List<ExcelVo> excelVos = readExcelXlsx(new FileInputStream(new File(filePath)), 1);
        for (ExcelVo vo : excelVos) {
            log.info("=======================读取表格{}数据开始=======================", vo.getSheetName());
            for (List<String> rowInfo : vo.getSheetData()) {
                log.info(rowInfo.toString());
            }
            log.info("=======================读取表格{}数据结束=======================", vo.getSheetName());
        }
        //------------------------------------------- 合并单元格 -------------------------------------
        filePath = "/Users/gaoxueyong/Documents/export/merge.xls";
        mergeCellTest(filePath);

    }


    /**
     * 读取xlsx格式大excel文件
     *
     * @param inputStream
     * @param startRowIndex 从第几行开始读 (1-> ~)
     * @return
     * @throws IOException
     */
    public static List<ExcelVo> readExcelXlsx(InputStream inputStream, Integer startRowIndex) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFRow row;
        XSSFSheet sheet;
        int lastCellNum;
        int readIndex = 0;
        if (null != startRowIndex && startRowIndex > 0) {
            readIndex = startRowIndex-1;
        }
        List<ExcelVo> result = new ArrayList<>();
        ExcelVo excelVo;
        List<List<String>> sheetData;
        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            excelVo = new ExcelVo();
            excelVo.setSheetName(workbook.getSheetName(sheetIndex));
            sheet = workbook.getSheetAt(sheetIndex);
            sheetData = new ArrayList<>(sheet.getLastRowNum());
            for (int i = readIndex; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                lastCellNum = row.getLastCellNum();
                List<String> rowInfo = new ArrayList<>(lastCellNum);
                for (int rowIndex = 0; rowIndex < lastCellNum; rowIndex++) {
                    rowInfo.add(row.getCell(rowIndex).getStringCellValue());
                }
                sheetData.add(rowInfo);
            }
            excelVo.setSheetData(sheetData);
            result.add(excelVo);
        }
        return result;
    }

    /**
     * 读取xls格式大excel文件
     *
     * @param inputStream
     * @param startRowIndex 从第几行开始读 (1-> ~)
     * @return
     * @throws IOException
     */
    public static List<ExcelVo> readExcelXls(InputStream inputStream, Integer startRowIndex) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFRow row;
        HSSFSheet sheet;
        int lastCellNum;
        int readIndex = 0;
        if (null != startRowIndex && startRowIndex > 0) {
            readIndex = startRowIndex -1;
        }
        List<ExcelVo> result = new ArrayList<>();
        ExcelVo excelVo;
        List<List<String>> sheetData;
        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            excelVo = new ExcelVo();
            excelVo.setSheetName(workbook.getSheetName(sheetIndex));
            sheet = workbook.getSheetAt(sheetIndex);
            sheetData = new ArrayList<>(sheet.getLastRowNum());
            for (int i = readIndex; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                lastCellNum = row.getLastCellNum();
                List<String> rowInfo = new ArrayList<>(lastCellNum);
                for (int rowIndex = 0; rowIndex < lastCellNum; rowIndex++) {
                    rowInfo.add(row.getCell(rowIndex).getStringCellValue());
                }
                sheetData.add(rowInfo);
            }
            excelVo.setSheetData(sheetData);
            result.add(excelVo);
        }
        return result;
    }

    /**
     * 生成 xlsx格式的excel
     *
     * @param filePath 文件路径  如 D:\export\xxx.xlsx
     * @param list     数据
     * @throws IOException
     */
    public static void writeExcelXlsx(String filePath, List<ExcelVo> list) throws IOException {

        long start = System.currentTimeMillis();
        // 声明一个工作薄
        XSSFWorkbook workBook = new XSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        start = System.currentTimeMillis();
        //校验文件路径是否存在
        String[] filePathAry = filePath.split(File.separator);
        File checkPath = new File(filePathAry[filePathAry.length - 1]);
        if (!checkPath.exists()) {
            checkPath.mkdirs();
        }

        File file = new File(filePath);
        //文件输出流
        FileOutputStream outStream = new FileOutputStream(file);
        workBook.write(outStream);
        outStream.flush();
        log.info("生产文件[{}]耗时:{}！", filePath, System.currentTimeMillis() - start);
        outStream.close();
    }

    public static void writeExcelXlsx(List<ExcelVo> list, OutputStream outputStream) throws IOException {
        long start = System.currentTimeMillis();
        // 声明一个工作薄
        XSSFWorkbook workBook = new XSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        workBook.write(outputStream);
        outputStream.close();
    }

    public static byte[] writeExcelXlsx(List<ExcelVo> list) throws IOException {
        long start = System.currentTimeMillis();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        // 声明一个工作薄
        XSSFWorkbook workBook = new XSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        workBook.write(os);
        return os.toByteArray();
    }

    /**
     * 生成 xls格式的excel
     *
     * @param filePath 文件路径  如 D:\export\xxx.xls
     * @param list     数据
     * @throws IOException
     */
    public static void writeExcelXls(String filePath, List<ExcelVo> list) throws IOException {

        long start = System.currentTimeMillis();
        // 声明一个工作薄
        HSSFWorkbook workBook = new HSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        start = System.currentTimeMillis();
        //校验文件路径是否存在
        String[] filePathAry = filePath.split(File.separator);
        File checkPath = new File(filePathAry[filePathAry.length - 1]);
        if (!checkPath.exists()) {
            checkPath.mkdirs();
        }

        File file = new File(filePath);
        //文件输出流
        FileOutputStream outStream = new FileOutputStream(file);
        workBook.write(outStream);
        outStream.flush();
        log.info("生产文件[{}]耗时:{}！", filePath, System.currentTimeMillis() - start);
        outStream.close();
    }

    public static void writeExcelXls(List<ExcelVo> list, OutputStream outputStream) throws IOException {
        long start = System.currentTimeMillis();
        // 声明一个工作薄
        HSSFWorkbook workBook = new HSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        workBook.write(outputStream);
        outputStream.close();
    }

    public static byte[] writeExcelXls(List<ExcelVo> list) throws IOException {
        long start = System.currentTimeMillis();
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        // 声明一个工作薄
        HSSFWorkbook workBook = new HSSFWorkbook();
        writeData(list, workBook);
        log.info("写入数据耗时:{}", System.currentTimeMillis() - start);
        workBook.write(os);
        return os.toByteArray();
    }

    private static void writeData(List<ExcelVo> list, XSSFWorkbook workBook) {
        if (CollectionUtils.isEmpty(list)) {
            workBook.createSheet();
            return;
        }
        XSSFRow row;
        int sheetIndex = 0;
        for (ExcelVo vo : list) {
            String sheetName = vo.getSheetName();
            List<String> sheetHead = vo.getSheetHead();
            // 生成一个表格
            XSSFSheet sheet = workBook.createSheet();
//            sheet.addMergedRegion()
            if (CollectionUtils.isNotEmpty(vo.getCellAddresses())) {
                //合并单元格 CellRangeAddress cra = new CellRangeAddress(起始行,结束行 , 起始列, 结束列); 行和列都是从0开始
                for (CellRangeAddress cra : vo.getCellAddresses()) {
                    sheet.addMergedRegion(cra);
                }
            }

            if (StringUtils.isNotEmpty(sheetName)) {
                workBook.setSheetName(sheetIndex, sheetName);
            }
            int rowIndex = 0;
            if (CollectionUtils.isNotEmpty(sheetHead)) {
                // 创建表格标题行 第一行
                row = sheet.createRow(rowIndex);
                for (int index = 0; index < sheetHead.size(); index++) {
                    row.createCell(index).setCellValue(sheetHead.get(index));
                }
                rowIndex = 1;
            }
            //插入需导出的数据
            for (List<String> dataList : vo.getSheetData()) {
                row = sheet.createRow(rowIndex);
                for (int columnIndex = 0; columnIndex < dataList.size(); columnIndex++) {
                    row.createCell(columnIndex).setCellValue(dataList.get(columnIndex));
                }
                rowIndex++;
            }
            sheetIndex++;
        }
    }

    private static void writeData(List<ExcelVo> list, HSSFWorkbook workBook) {
        if (CollectionUtils.isEmpty(list)) {
            workBook.createSheet();
            return;
        }
        HSSFRow row;
        int sheetIndex = 0;
        for (ExcelVo vo : list) {
            String sheetName = vo.getSheetName();
            List<String> sheetHead = vo.getSheetHead();
            // 生成一个表格
            HSSFSheet sheet = workBook.createSheet();
//            sheet.addMergedRegion()
            if (CollectionUtils.isNotEmpty(vo.getCellAddresses())) {
                //合并单元格 CellRangeAddress cra = new CellRangeAddress(起始行,结束行 , 起始列, 结束列); 行和列都是从0开始
                for (CellRangeAddress cra : vo.getCellAddresses()) {
                    sheet.addMergedRegion(cra);
                }
            }

            if (StringUtils.isNotEmpty(sheetName)) {
                workBook.setSheetName(sheetIndex, sheetName);
            }
            int rowIndex = 0;
            if (CollectionUtils.isNotEmpty(sheetHead)) {
                // 创建表格标题行 第一行
                row = sheet.createRow(rowIndex);
                for (int index = 0; index < sheetHead.size(); index++) {
                    row.createCell(index).setCellValue(sheetHead.get(index));
                }
                rowIndex = 1;
            }
            //插入需导出的数据
            for (List<String> dataList : vo.getSheetData()) {
                row = sheet.createRow(rowIndex);
                for (int columnIndex = 0; columnIndex < dataList.size(); columnIndex++) {
                    row.createCell(columnIndex).setCellValue(dataList.get(columnIndex));
                }
                rowIndex++;
            }
            sheetIndex++;
        }
    }

    public static void mergeCellTest(String filePath) throws IOException {
        String[] filePathAry = filePath.split(File.separator);
        String fileName = filePathAry[filePathAry.length - 1];
        List<ExcelVo> list = new ArrayList<>();
        ExcelVo excelVo = new ExcelVo();
//        excelVo.setSheetName("第一个sheet");
//        excelVo.setSheetHead(Arrays.asList("a","b","c"));
        List<List<String>> sheetData = new ArrayList<>();
        sheetData.add(Arrays.asList("a1", "a2", "a3", "a4"));
        sheetData.add(Arrays.asList("a1", "a2", "a3", "a5"));
        sheetData.add(Arrays.asList("a5", "a6", "a7", "a6"));
        sheetData.add(Arrays.asList("a5", "a6", "a7", "a8"));
        excelVo.setSheetData(sheetData);


//         CellRangeAddress cra = new CellRangeAddress(起始行,结束行 , 起始列, 结束列);
        //从第1列至第3列 每列都需要合并
        List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
        cellRangeAddressList.add(new CellRangeAddress(0, 1, 0, 0));
        cellRangeAddressList.add(new CellRangeAddress(0, 1, 1, 1));
        cellRangeAddressList.add(new CellRangeAddress(0, 1, 2, 2));

        cellRangeAddressList.add(new CellRangeAddress(2, 3, 0, 0));
        cellRangeAddressList.add(new CellRangeAddress(2, 3, 1, 1));
        cellRangeAddressList.add(new CellRangeAddress(2, 3, 2, 2));
        excelVo.setCellAddresses(cellRangeAddressList);
        list.add(excelVo);

        excelVo = new ExcelVo();
//        excelVo.setSheetName("第二个sheet");
//        excelVo.setSheetHead(Arrays.asList("A","B","C"));
        sheetData = new ArrayList<>();
        sheetData.add(Arrays.asList("A1", "A2", "A3", "A4"));
        sheetData.add(Arrays.asList("A5", "A6", "A7"));
        excelVo.setSheetData(sheetData);
        list.add(excelVo);
        writeExcelXlsx(filePath, list);
    }

    /**
     * 读取网络xlsx文件
     * @param url
     * @param method "GET", "POST"
     */
    public static List<ExcelVo>  readXlsxByUrl(String url, String method) {
        HttpURLConnection conn = null;
        InputStream inputStream = null;
        try {
// 建立链接
            URL httpUrl = new URL(url);
            conn = (HttpURLConnection) httpUrl.openConnection();
//以Post方式提交表单，默认get方式
            conn.setUseCaches(false);
            if(StringUtils.isBlank(method) || StringUtils.equals(HttpMethod.GET.name(),method)){
                // post方式不能使用缓存  get可以
                conn.setUseCaches(true);
            }
            conn.setRequestMethod(method);
//            conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
            conn.setDoInput(true);
            conn.setDoOutput(true);
//连接指定的资源
            conn.connect();
//获取网络输入流
            inputStream = conn.getInputStream();
            List<ExcelVo> excelVos = ExcelUtils.readExcelXlsx(inputStream, null);
            conn.disconnect();
            return excelVos;
        } catch (Exception e) {
            log.error("读取网络文件[{}]异常！",url,e);
            IOUtils.closeQuietly(inputStream);
            conn.disconnect();
            return null;
        }
    }
}
